I'm running PowerPivots in Excel 2010.  When I renamed the sheets in the PowerPivot window and then went back to the related pivot tables and refreshed them I got the following error and the pivot tables where disconnected completely from there data source and destroyed.  I read a related post on this in MSN that indicated that refreshing the data sources in the PowerPivot windows resolves this problem, but it didn't in my case.  Can anyone suggest the protocol for resolving this?

Thanks,

...bob

This can happen with customer that copy and paste pivot tables from one spreadsheet to another. The problem happens if the different spreadsheets happen to have connections with the same name but point to different data sources. When you do the copy/paste operation Excel considers that the connections with the same name are the same and replaces the connection information on the current spreadsheet with the content of the connection of the pivot table you are pasting. The problem does not happen until you close the excel file, open again and refresh the pivot table.

It seems to me that the issue is because excel is not checking if the connections are 100% the same. We should prompt to create a new connection when we see the difference, this would avoid the problem.

The workaround is to make sure your connections have different names when you are doing copy and paste operations with pivot tables.

Cheers,
Tony Chen
Forum Support
________________________________________
Come back and mark the replies as answers if they help and unmark them if they provide no help.
If you have any feedback on our support, please contact tnmff@microsoft.com.

• Edited by Tony Chen CHNMicrosoft contingent staff, Moderator 9 hours 5 minutes ago

This can happen with customer that copy and paste pivot tables from one spreadsheet to another. The problem happens if the different spreadsheets happen to have connections with the same name but point to different data sources. When you do the copy/paste operation Excel considers that the connections with the same name are the same and replaces the connection information on the current spreadsheet with the content of the connection of the pivot table you are pasting. The problem does not happen until you close the excel file, open again and refresh the pivot table.

It seems to me that the issue is because excel is not checking if the connections are 100% the same. We should prompt to create a new connection when we see the difference, this would avoid the problem.

The workaround is to make sure your connections have different names when you are doing copy and paste operations with pivot tables.

Cheers,
Tony Chen
Forum Support
________________________________________
Come back and mark the replies as answers if they help and unmark them if they provide no help.
If you have any feedback on our support, please contact tnmff@microsoft.com.

• Edited by Tony Chen CHNMicrosoft contingent staff, Moderator Friday, November 22, 2013 2:55 AM

This can happen with customer that copy and paste pivot tables from one spreadsheet to another. The problem happens if the different spreadsheets happen to have connections with the same name but point to different data sources. When you do the copy/paste operation Excel considers that the connections with the same name are the same and replaces the connection information on the current spreadsheet with the content of the connection of the pivot table you are pasting. The problem does not happen until you close the excel file, open again and refresh the pivot table.

It seems to me that the issue is because excel is not checking if the connections are 100% the same. We should prompt to create a new connection when we see the difference, this would avoid the problem.

The workaround is to make sure your connections have different names when you are doing copy and paste operations with pivot tables.

Cheers,
Tony Chen
Forum Support
________________________________________
Come back and mark the replies as answers if they help and unmark them if they provide no help.
If you have any feedback on our support, please contact tnmff@microsoft.com.

• Edited by Tony Chen CHNMicrosoft contingent staff, Moderator Friday, November 22, 2013 2:55 AM

This can happen with customer that copy and paste pivot tables from one spreadsheet to another. The problem happens if the different spreadsheets happen to have connections with the same name but point to different data sources. When you do the copy/paste operation Excel considers that the connections with the same name are the same and replaces the connection information on the current spreadsheet with the content of the connection of the pivot table you are pasting. The problem does not happen until you close the excel file, open again and refresh the pivot table.

It seems to me that the issue is because excel is not checking if the connections are 100% the same. We should prompt to create a new connection when we see the difference, this would avoid the problem.

The workaround is to make sure your connections have different names when you are doing copy and paste operations with pivot tables.

Cheers,
Tony Chen
Forum Support
________________________________________
Come back and mark the replies as answers if they help and unmark them if they provide no help.
If you have any feedback on our support, please contact tnmff@microsoft.com.

• Edited by Tony Chen CHNMicrosoft contingent staff, Moderator Friday, November 22, 2013 2:55 AM

I'm not copying and pasting data.  The connection is to a SQL Server View and of course there can't be two views with the same name in the same database instance.  As pointed out in my original post, the error only appears when I leave the PowerPivot window (after refreshing PowerPivot connection, after changing sheet name) and then refresh any of the pivot tables using the data related to the connection sheet.  At that point the pivot table is blank and needs to be completely rebuilt.  These pivots are complex and time consuming to rebuild.

Thanks for the input, even though it was not on point.

...bob

Hi Bob,

I am trying to involve someone familiar with this topic to further look at this issue. Appreciate your patience.

Cheers,
Tony Chen
Forum Support
________________________________________
Come back and mark the replies as answers if they help and unmark them if they provide no help.
If you have any feedback on our support, please contact tnmff@microsoft.com.

Hi Bob,

Can you create a new Power Pivot and test the same behavior on the machine?
I think the connection that is created will still point to the old name.
Please check the table name in the connection query.

Regards,
Rohit.

I'm removing this off the forum since I have received no helpful replies.

I do appreciate those individuals who at least tried. 

Thanks,

...bob